# app.py

import uvicorn
from fastapi import FastAPI, Depends, HTTPException, Request, status
from fastapi.responses import HTMLResponse, RedirectResponse
from fastapi.templating import Jinja2Templates
from sqlalchemy import create_engine, Column, Integer, String, ForeignKey, CHAR, BIGINT, ForeignKeyConstraint, text
from sqlalchemy.orm import sessionmaker, Session, relationship, declarative_base
from pydantic import BaseModel, Field
from typing import List, Optional
from datetime import datetime
from passlib.context import CryptContext
from sqlalchemy.orm import joinedload
from fastapi.staticfiles import StaticFiles

# 1. ================== 配置 ==================
# 请修改为您自己的MySQL数据库连接信息
# 格式: "mysql+mysqlclient://<用户名>:<密码>@<主机>:<端口>/<数据库名>"
DATABASE_URL = "mysql://root:123456@localhost:3306/hospital_test"

# 密码哈希
pwd_context = CryptContext(schemes=["bcrypt"], deprecated="auto")

# 2. ================== 数据库设置 ==================
engine = create_engine(DATABASE_URL)
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)
Base = declarative_base()


# 数据库会话依赖
def get_db():
    db = SessionLocal()
    try:
        yield db
    finally:
        db.close()


# 3. ================== 数据库模型 (Tables) ==================
class Department(Base):
    __tablename__ = "departments"
    id = Column(Integer, primary_key=True, index=True)
    name = Column(String(50), unique=True, nullable=False, index=True)
    doctors = relationship("Doctor", back_populates="department")


class Doctor(Base):
    __tablename__ = "doctors"
    id = Column(String(50), primary_key=True, index=True) # 工号
    name = Column(String(50), nullable=False)
    hashed_password = Column(String(100), nullable=False)
    department_id = Column(Integer, ForeignKey("departments.id"), index=True) # 索引
    title = Column(String(50), nullable=True) # 职称字段
    department = relationship("Department", back_populates="doctors")
    __table_args__ = (
        ForeignKeyConstraint(
            ['department_id'], ['departments.id'], name='fk_doctors_department_id'
        ),
    )

class Patient(Base):
    __tablename__ = "patients"
    # 将 ID 修改为 VARCHAR
    id = Column(String(50), primary_key=True, index=True) # 病历号
    name = Column(String(50), nullable=False)
    gender = Column(CHAR(1), nullable=True) # 'M' or 'F'
    phone_number = Column(String(20), nullable=True, index=True) # 新增电话号码字段, 添加索引
    hashed_password = Column(String(100), nullable=False)


class Appointment(Base):
    __tablename__ = "appointments"
    id = Column(Integer, primary_key=True, index=True)
    patient_id = Column(String(50), ForeignKey("patients.id"), index=True) # 外键类型修改为 String, 添加索引
    doctor_id = Column(String(50), ForeignKey("doctors.id"), index=True) # 外键类型修改为 String, 添加索引
    appointment_date = Column(String(20), default=lambda: datetime.now().strftime("%Y-%m-%d %H:%M"), index=True) # 添加索引
    status = Column(String(20), default="已预约")  # 例如: 已预约, 已就诊, 已取消

    patient = relationship("Patient")
    doctor = relationship("Doctor")
    prescriptions = relationship("Prescription", back_populates="appointment")
    __table_args__ = (
        ForeignKeyConstraint(
            ['patient_id'], ['patients.id'], name='fk_appointments_patient_id'
        ),
        ForeignKeyConstraint(
            ['doctor_id'], ['doctors.id'], name='fk_appointments_doctor_id'
        ),
    )


class Prescription(Base):
    __tablename__ = "prescriptions"
    id = Column(Integer, primary_key=True, index=True)
    appointment_id = Column(Integer, ForeignKey("appointments.id"), index=True) # 添加索引
    item_name = Column(String(100))  # 药品或诊疗项目名称
    details = Column(String(255))  # 用法用量或备注

    appointment = relationship("Appointment", back_populates="prescriptions")
    __table_args__ = (
        ForeignKeyConstraint(
            ['appointment_id'], ['appointments.id'], name='fk_prescriptions_appointment_id'
        ),
    )

# 新增 ActiveAppointment 视图模型
class ActiveAppointment(Base):
    __tablename__ = "active_appointments_view"
    # 视图没有主键概念，但为了ORM映射，通常选择一个唯一性较好的列作为伪主键
    # 或者直接使用复合主键，这里我们使用 appointment_id 作为主要识别。
    appointment_id = Column(Integer, primary_key=True) # 使用 appointment_id 作为视图的伪主键
    appointment_date = Column(String(20))
    status = Column(String(20))
    patient_id = Column(String(50))
    patient_name = Column(String(50))
    patient_phone_number = Column(String(20))
    doctor_id = Column(String(50))
    doctor_name = Column(String(50))
    doctor_title = Column(String(50))
    department_name = Column(String(50))


# 4. ================== Pydantic 模型 (Schemas for API) ==================
class DepartmentSchema(BaseModel):
    id: int
    name: str
    class Config:
        from_attributes = True


class DoctorBase(BaseModel):
    id: str = Field(pattern="^doc.*") # 类型修改为 str, 添加格式验证
    name: str
    title: Optional[str] = None # 新增职称字段
    class Config: # <--- 确保 DoctorBase 也有
        from_attributes = True

class DoctorCreate(DoctorBase):
    department_id: int
    password: str


class DoctorSchema(DoctorBase):
    department: DepartmentSchema
    class Config:
        from_attributes = True


class PatientBase(BaseModel):
    id: str = Field(pattern="^pat.*") # 类型修改为 str, 添加格式验证
    name: str
    gender: Optional[str] = None
    phone_number: Optional[str] = None # 新增电话号码字段
    class Config: # <--- 确保 PatientBase 也有
        from_attributes = True

class PatientCreate(PatientBase):
    password: str


class PatientSchema(PatientBase):
    class Config:
        from_attributes = True

class PrescriptionSchema(BaseModel):
    id: int
    item_name: str
    details: str
    class Config:
        from_attributes = True

class AppointmentSchema(BaseModel):
    id: int
    appointment_date: str
    status: str
    doctor: DoctorSchema
    patient: PatientBase
    prescriptions: List[PrescriptionSchema] = []
    class Config:
        from_attributes = True

# 新增 ActiveAppointmentSchema 视图 Schema
class ActiveAppointmentSchema(BaseModel):
    appointment_id: int
    appointment_date: str
    status: str
    patient_id: str
    patient_name: str
    patient_phone_number: Optional[str] = None
    doctor_id: str
    doctor_name: str
    doctor_title: Optional[str] = None
    department_name: str

    class Config:
        from_attributes = True

class AppointmentCreate(BaseModel):
    patient_id: str # 类型修改为 str
    doctor_id: str # 类型修改为 str

class PrescriptionCreate(BaseModel):
    appointment_id: int
    item_name: str
    details: str

# 新增科室统计 Schema
class DepartmentStats(BaseModel):
    department_name: str
    doctor_count: int
    total_active_appointments: int

    class Config:
        from_attributes = True

# 新增：总体统计 Schema
class OverallStats(BaseModel):
    total_doctors: int
    total_patients: int
    total_appointments: int
    total_prescriptions: int
    avg_active_appointments_per_doctor: float # 平均值可以是浮点数

    class Config:
        from_attributes = True

# 5. ================== FastAPI 应用实例和模板 ==================
app = FastAPI()
templates = Jinja2Templates(directory="templates")

app.mount("/static", StaticFiles(directory="static"), name="static")

# 自动创建数据库表 (仅在首次运行时需要)
Base.metadata.create_all(bind=engine)


# 6. ================== 辅助函数 ==================
def verify_password(plain_password, hashed_password):
    # 在这个临时调试阶段，如果hashed_password是明文，直接比较
    if not hashed_password.startswith("$2b$"):
        return plain_password == hashed_password
    return pwd_context.verify(plain_password, hashed_password)


def get_password_hash(password):
    # 临时修改：在调试阶段直接返回明文密码，之后会改回来
    return password


# 7. ================== API 路由 ==================

# --- 医生 API ---
@app.post("/api/doctors/", response_model=DoctorSchema, status_code=status.HTTP_201_CREATED)
def create_doctor(doctor: DoctorCreate, db: Session = Depends(get_db)):
    db_doctor = db.query(Doctor).filter(Doctor.id == doctor.id).first()
    if db_doctor:
        raise HTTPException(status_code=400, detail="该工号已存在")
    # 生成基于ID的密码，并哈希
    numeric_part = doctor.id[3:] # 提取数字部分
    generated_password = numeric_part + "123"
    hashed_password = get_password_hash(generated_password)
    new_doctor = Doctor(id=doctor.id, name=doctor.name, department_id=doctor.department_id, title=doctor.title, hashed_password=hashed_password)
    db.add(new_doctor)
    db.commit()
    db.refresh(new_doctor)
    return new_doctor


@app.get("/api/doctors/", response_model=List[DoctorSchema])
def get_all_doctors(db: Session = Depends(get_db)):
    return db.query(Doctor).all()


@app.get("/api/doctors/{doctor_id}", response_model=DoctorSchema) # 修改路径参数类型
def get_doctor_by_id(doctor_id: str, db: Session = Depends(get_db)):
    db_doctor = db.query(Doctor).filter(Doctor.id == doctor_id).first()
    if db_doctor is None:
        raise HTTPException(status_code=404, detail="医生未找到")
    return db_doctor

# --- 病人 API ---
@app.post("/api/patients/", response_model=PatientSchema, status_code=status.HTTP_201_CREATED)
def create_patient(patient: PatientCreate, db: Session = Depends(get_db)):
    db_patient = db.query(Patient).filter(Patient.id == patient.id).first()
    if db_patient:
        raise HTTPException(status_code=400, detail="该病历号已存在")
    # 生成基于ID的密码，并哈希
    numeric_part = patient.id[3:] # 提取数字部分
    generated_password = numeric_part + "123"
    hashed_password = get_password_hash(generated_password)
    new_patient = Patient(id=patient.id, name=patient.name, gender=patient.gender, phone_number=patient.phone_number, hashed_password=hashed_password)
    db.add(new_patient)
    db.commit()
    db.refresh(new_patient)
    return new_patient


@app.get("/api/patients/", response_model=List[PatientSchema])
def get_all_patients(db: Session = Depends(get_db)):
    return db.query(Patient).all()

@app.get("/api/patients/{patient_id}", response_model=PatientSchema) # 修改路径参数类型
def get_patient_by_id(patient_id: str, db: Session = Depends(get_db)):
    db_patient = db.query(Patient).filter(Patient.id == patient_id).first()
    if db_patient is None:
        raise HTTPException(status_code=404, detail="病人未找到")
    return db_patient

# --- 科室 API ---
@app.get("/api/departments/", response_model=List[DepartmentSchema])
def get_all_departments(db: Session = Depends(get_db)):
    return db.query(Department).all()


# --- 活跃预约视图 API ---
@app.get("/api/active_appointments/", response_model=List[ActiveAppointmentSchema])
def get_active_appointments(db: Session = Depends(get_db)):
    return db.query(ActiveAppointment).all()


# --- 仪表板统计 API ---
@app.get("/api/dashboard/department_stats/", response_model=List[DepartmentStats])
async def get_department_stats(db: Session = Depends(get_db)):
    query = text("""
        WITH DepartmentDoctorCount AS (
            SELECT
                d.department_id,
                dep.name AS department_name,
                COUNT(d.id) AS doctor_count
            FROM
                doctors AS d
            JOIN
                departments AS dep ON d.department_id = dep.id
            GROUP BY
                d.department_id, dep.name
        ),
        DepartmentActiveAppointmentCount AS (
            SELECT
                d.department_id,
                COUNT(a.id) AS active_appointment_count
            FROM
                appointments AS a
            JOIN
                doctors AS d ON a.doctor_id = d.id
            WHERE
                a.status = '已预约'
            GROUP BY
                d.department_id
        )
        SELECT
            ddc.department_name,
            ddc.doctor_count,
            COALESCE(daac.active_appointment_count, 0) AS total_active_appointments
        FROM
            DepartmentDoctorCount AS ddc
        LEFT JOIN
            DepartmentActiveAppointmentCount AS daac ON ddc.department_id = daac.department_id
        ORDER BY
            ddc.department_name;
    """)
    result = db.execute(query).fetchall()
    return [DepartmentStats.model_validate(dict(row._mapping)) for row in result]

# 新增：总体统计 API ---
@app.get("/api/dashboard/overall_stats/", response_model=OverallStats)
async def get_overall_stats(db: Session = Depends(get_db)):
    query = text("""
        SELECT
            COUNT(DISTINCT d.id) AS total_doctors,
            COUNT(DISTINCT p.id) AS total_patients,
            COUNT(DISTINCT a.id) AS total_appointments,
            COUNT(DISTINCT pr.id) AS total_prescriptions,
            COALESCE(ROUND(SUM(CASE WHEN a.status = '已预约' THEN 1 ELSE 0 END) / COUNT(DISTINCT d.id), 2), 0) AS avg_active_appointments_per_doctor
        FROM
            doctors d, -- 使用 JOIN 代替逗号分隔，更清晰
            patients p, -- 这里交叉连接是为了获取所有表数据，但如果仅需计数，直接 FROM table 即可
            appointments a,
            prescriptions pr;
    """)
    result = db.execute(query).fetchone() # 只需要一条结果
    if result:
        return OverallStats.model_validate(dict(result._mapping))
    # 如果没有数据，返回一个默认的 OverallStats 对象
    return OverallStats(total_doctors=0, total_patients=0, total_appointments=0, total_prescriptions=0, avg_active_appointments_per_doctor=0.0)

# 8. ================== 页面路由 ==================
@app.get("/", response_class=HTMLResponse)
async def serve_login_page(request: Request):
    return templates.TemplateResponse("login.html", {"request": request})


@app.post("/login", response_class=HTMLResponse)  # 返回HTMLResponse以处理错误情况
async def handle_login(request: Request, db: Session = Depends(get_db)):
    """
    处理用户登录请求，验证凭据并根据角色重定向。
    """

    form_data = await request.form()
    user_id: str = form_data.get("user_id")
    password: str = form_data.get("password")

    # --- DEBUGGING START --- #
    print(f"DEBUG: Received user_id: {user_id} (type: {type(user_id)})")
    print(f"DEBUG: Received password: {password} (type: {type(password)})\n") # 添加一个空行，让输出更清晰
    # --- DEBUGGING END --- #

    if not user_id or not password:
        error_msg = "用户ID和密码都不能为空"
        print(f"[LOGIN FAILED] Reason: {error_msg}")
        return templates.TemplateResponse("login.html", {"request": request, "error": error_msg}, status_code=400)

    # 根据用户ID判断角色并进行验证
    redirect_url = None

    # === 管理员验证 ===
    if user_id == "admin999": # 假设管理员ID是 "admin999"
        print("[LOGIN INFO] Role detected: Admin")
        expected_plain_password = "999123" # 管理员密码
        if verify_password(password, get_password_hash(expected_plain_password)):
            redirect_url = f"/admin"
            print("[LOGIN SUCCESS] Admin logged in.")
        else:
            error_msg = "管理员密码错误"
            print(f"[LOGIN FAILED] Reason: {error_msg}")
            return templates.TemplateResponse("login.html", {"request": request, "error": error_msg}, status_code=401)

    # === 医生验证 ===
    elif user_id.startswith("doc"):
        print("[LOGIN INFO] Role detected: Doctor")
        doctor = db.query(Doctor).filter(Doctor.id == user_id).first()
        if doctor:
            # 动态生成医生预期密码
            numeric_part = user_id[3:]
            if not numeric_part.isdigit(): # 确保提取的数字部分是纯数字
                error_msg = "医生工号格式错误，请检查工号后三位是否为数字"
                print(f"[LOGIN FAILED] Reason: {error_msg}")
                return templates.TemplateResponse("login.html", {"request": request, "error": error_msg}, status_code=400)
            expected_plain_password = numeric_part + "123"
            if verify_password(password, get_password_hash(expected_plain_password)):
                redirect_url = f"/doctor/{doctor.id}"
                print(f"[LOGIN SUCCESS] Doctor '{user_id}' logged in.")
            else:
                error_msg = "无效的医生工号或密码错误"
                print(f"[LOGIN FAILED] Reason: {error_msg}")
                return templates.TemplateResponse("login.html", {"request": request, "error": error_msg}, status_code=401)
        else:
            error_msg = "无效的医生工号或密码错误"
            print(f"[LOGIN FAILED] Reason: {error_msg}")
            return templates.TemplateResponse("login.html", {"request": request, "error": error_msg}, status_code=401)

    # === 病人验证 ===
    elif user_id.startswith("pat"):
        print("[LOGIN INFO] Role detected: Patient")
        patient = db.query(Patient).filter(Patient.id == user_id).first()
        if patient:
            # 动态生成病人预期密码
            numeric_part = user_id[3:]
            if not numeric_part.isdigit(): # 确保提取的数字部分是纯数字
                error_msg = "病历号格式错误，请检查病历号后三位是否为数字"
                print(f"[LOGIN FAILED] Reason: {error_msg}")
                return templates.TemplateResponse("login.html", {"request": request, "error": error_msg}, status_code=400)
            expected_plain_password = numeric_part + "123"
            if verify_password(password, get_password_hash(expected_plain_password)):
                redirect_url = f"/patient/{patient.id}"
                print(f"[LOGIN SUCCESS] Patient '{user_id}' logged in.")
            else:
                error_msg = "无效的病历号或密码错误"
                print(f"[LOGIN FAILED] Reason: {error_msg}")
                return templates.TemplateResponse("login.html", {"request": request, "error": error_msg}, status_code=401)
        else:
            error_msg = "无效的病历号或密码错误"
            print(f"[LOGIN FAILED] Reason: {error_msg}")
            return templates.TemplateResponse("login.html", {"request": request, "error": error_msg}, status_code=401)

    else:
        error_msg = "无效的用户ID或角色"
        print(f"[LOGIN FAILED] Reason: {error_msg}")
        return templates.TemplateResponse("login.html", {"request": request, "error": error_msg}, status_code=400)

    # 重定向到对应的 Dashboard
    if redirect_url:
        response = RedirectResponse(url=redirect_url, status_code=status.HTTP_302_FOUND)
        # response.set_cookie(key="session_id", value="some_session_id", httpy=True) # 可以添加会话管理
        return response

@app.get("/admin", response_class=HTMLResponse)
async def serve_admin_dashboard(request: Request):
    return templates.TemplateResponse("admin_dashboard.html", {"request": request})


@app.get("/doctor/{doctor_id}", response_class=HTMLResponse)
async def serve_doctor_dashboard(request: Request, doctor_id: str, db: Session = Depends(get_db)):
    doctor = db.query(Doctor).filter(Doctor.id == doctor_id).options(joinedload(Doctor.department)).first()
    if not doctor:
        raise HTTPException(status_code=404, detail="医生未找到")
    appointments = db.query(Appointment).filter(Appointment.doctor_id == doctor_id).options(joinedload(Appointment.patient)).all()
    return templates.TemplateResponse("doctor_dashboard.html", {"request": request, "doctor": doctor, "appointments": appointments})


@app.get("/patient/{patient_id}", response_class=HTMLResponse)
async def serve_patient_dashboard(request: Request, patient_id: str, db: Session = Depends(get_db)):
    patient = db.query(Patient).filter(Patient.id == patient_id).first()
    if not patient:
        raise HTTPException(status_code=404, detail="病人未找到")
    appointments = db.query(Appointment).filter(Appointment.patient_id == patient_id).options(joinedload(Appointment.doctor)).all()
    return templates.TemplateResponse("patient_dashboard.html", {"request": request, "patient": patient, "appointments": appointments})


# --- 预约 API ---
@app.post("/api/appointments/", response_model=AppointmentSchema)
def create_appointment(appointment: AppointmentCreate, db: Session = Depends(get_db)):
    db_patient = db.query(Patient).filter(Patient.id == appointment.patient_id).first()
    db_doctor = db.query(Doctor).filter(Doctor.id == appointment.doctor_id).first()
    if not db_patient or not db_doctor:
        raise HTTPException(status_code=404, detail="病人或医生未找到")

    new_appointment = Appointment(patient_id=appointment.patient_id, doctor_id=appointment.doctor_id)
    db.add(new_appointment)
    db.commit()
    db.refresh(new_appointment)
    return new_appointment


@app.get("/api/appointments/doctor/{doctor_id}", response_model=List[AppointmentSchema])
def get_appointments_for_doctor(doctor_id: str, db: Session = Depends(get_db)):
    return db.query(Appointment).filter(Appointment.doctor_id == doctor_id).options(joinedload(Appointment.patient)).all()


@app.get("/api/appointments/patient/{patient_id}", response_model=List[AppointmentSchema])
def get_appointments_for_patient(patient_id: str, db: Session = Depends(get_db)):
    return db.query(Appointment).filter(Appointment.patient_id == patient_id).options(joinedload(Appointment.doctor)).all()


@app.put("/api/appointments/{appointment_id}/complete")
def complete_appointment(appointment_id: int, db: Session = Depends(get_db)):
    db_appointment = db.query(Appointment).filter(Appointment.id == appointment_id).first()
    if db_appointment is None:
        raise HTTPException(status_code=404, detail="预约未找到")
    db_appointment.status = "已就诊"
    db.commit()
    db.refresh(db_appointment)
    return {"message": "预约已完成"}


# --- 处方 API ---
@app.post("/api/prescriptions/", response_model=PrescriptionSchema)
def create_prescription(prescription: PrescriptionCreate, db: Session = Depends(get_db)):
    db_appointment = db.query(Appointment).filter(Appointment.id == prescription.appointment_id).first()
    if db_appointment is None:
        raise HTTPException(status_code=404, detail="预约未找到")
    new_prescription = Prescription(appointment_id=prescription.appointment_id, item_name=prescription.item_name, details=prescription.details)
    db.add(new_prescription)
    db.commit()
    db.refresh(new_prescription)
    return new_prescription


# 运行服务器 (可选，方便直接运行此文件)
if __name__ == "__main__":
    uvicorn.run(app, host="127.0.0.1", port=8000)